﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Linq;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using DevExpress.Utils;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Excel;

namespace DevEx.QuocSy.UserControl
{
    public partial class ucBaoCaoHangTon : DevExpress.XtraEditors.XtraUserControl
    {
        IQueryable listTon;

        public ucBaoCaoHangTon()
        {
            InitializeComponent();
        }

        private void ucBaoCaoHangTon_Load(object sender, EventArgs e)
        {
            //2. Danh muc Kho
            CKho m_kho = new CKho();
            lkeKho.Properties.DisplayMember = "Kho";
            lkeKho.Properties.ValueMember = "ID_Kho";            
            lkeKho.Properties.DataSource = m_kho.SelectAllRecord();
            lkeKho.EditValue = GlobalVar.CurrentUser.ID_Kho;

            //Khoi tao luon
            int ID_Kho = Convert.ToInt32(lkeKho.EditValue);
            KhoiTaoBaoCao(ID_Kho);
            FormatdtgBaoCaoNhapXuat();
                
        }

        private void btnBaoCao_Click(object sender, EventArgs e)
        {
            int ID_Kho = Convert.ToInt32(lkeKho.EditValue);
            KhoiTaoBaoCao(ID_Kho);            
        }

        public void KhoiTaoBaoCao(int ID_Kho)
        {
            
            QLVTDataContext dc = new QLVTDataContext();

            listTon = from p in dc.tblTons
                      where p.ID_Kho == ID_Kho
                      orderby p.tblVatTu.Ma_LoaiVatTu, p.SoTheKho, p.Code
                      select new
                      {
                          SoTheKho = p.SoTheKho + "-" + p.Code.ToString(),
                          TangKeKhoang = p.TangKeKhoang,
                          KH_NguonVatTu = p.tblNguonVatTu.KH_NguonVatTu,
                          SoHopDong = p.tblHopDong.SoHopDong,
                          VatTu = p.tblVatTu.VatTu,
                          MaKyHieu = p.tblVatTu.MaKyHieu,
                          NamSX = p.NamSX,
                          CCL = p.tblCCL.CCL,
                          DVT = p.tblVatTu.tblDVT.DVT,
                          Gia = p.Gia,
                          SLTonKH = p.SLTonKH,
                          ThanhTien = p.Gia * p.SLTonKH,
                          MaLoaiVatTu = p.tblVatTu.Ma_LoaiVatTu,
                          ID_MatHang = p.ID_MatHang
                          
                      };

            dtgBaoCaoNhapXuat.DataSource = listTon;
        }

        public void FormatdtgBaoCaoNhapXuat()
        {
            gridView1.Columns.ColumnByFieldName("MaLoaiVatTu").Visible = false;
            gridView1.OptionsView.ShowFooter = true;
            //Columns VatTu
            var cl_VatTu = gridView1.Columns.ColumnByFieldName("VatTu");
            cl_VatTu.Width = 200;
            cl_VatTu.Caption = "Vật Tư";
            cl_VatTu.AppearanceCell.TextOptions.HAlignment = HorzAlignment.Near;
            cl_VatTu.OptionsColumn.AllowEdit = false;
            cl_VatTu.AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Center;

            //Columns CCL
            var cl_CCL = gridView1.Columns.ColumnByFieldName("CCL");
            cl_CCL.Width = 40;
            cl_CCL.OptionsColumn.AllowEdit = false;
            cl_CCL.Caption = "CCL";
            cl_CCL.AppearanceCell.TextOptions.HAlignment = HorzAlignment.Center;
            cl_CCL.AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Center;

            //Columns Nguon Tai Chinh
            var cl_NguonVatTu = gridView1.Columns.ColumnByFieldName("KH_NguonVatTu");
            cl_NguonVatTu.Width = 40;
            cl_NguonVatTu.OptionsColumn.AllowEdit = false;
            cl_NguonVatTu.Caption = "N. TC";
            cl_NguonVatTu.AppearanceCell.TextOptions.HAlignment = HorzAlignment.Center;
            cl_NguonVatTu.AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Center;

            //Columns Nguon Nhap
            var cl_NguonNhap = gridView1.Columns.ColumnByFieldName("SoHopDong");
            cl_NguonNhap.Width = 70;
            cl_NguonNhap.OptionsColumn.AllowEdit = false;
            cl_NguonNhap.Caption = "Nguồn gốc";
            cl_NguonNhap.AppearanceCell.TextOptions.HAlignment = HorzAlignment.Near;
            cl_NguonNhap.AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Center;

            //Columns NamSX
            var cl_NamSX = gridView1.Columns.ColumnByFieldName("NamSX");
            cl_NamSX.Width = 70;
            cl_NamSX.OptionsColumn.AllowEdit = false;
            cl_NamSX.Caption = "Năm SX";
            cl_NamSX.AppearanceCell.TextOptions.HAlignment = HorzAlignment.Center;
            cl_NamSX.AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Center;

            //Columns MaKyHieu
            var cl_MaKyHieu = gridView1.Columns.ColumnByFieldName("MaKyHieu");
            cl_MaKyHieu.Caption = "Mã Hiệu";
            cl_MaKyHieu.Width = 150;
            cl_MaKyHieu.OptionsColumn.AllowEdit = false;
            cl_MaKyHieu.AppearanceCell.TextOptions.HAlignment = HorzAlignment.Near;
            cl_MaKyHieu.AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Center;

            //Columns SoTheKho
            var cl_SoTheKho = gridView1.Columns.ColumnByFieldName("SoTheKho");
            cl_SoTheKho.Width = 70;
            cl_SoTheKho.Caption = "Thẻ Kho";
            cl_SoTheKho.OptionsColumn.AllowEdit = false;
            cl_SoTheKho.AppearanceCell.TextOptions.HAlignment = HorzAlignment.Center;
            cl_SoTheKho.AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Center;

            //Columns DVT
            var cl_DVT = gridView1.Columns.ColumnByFieldName("DVT");
            cl_DVT.Width = 50;
            cl_DVT.Caption = "ĐVT";
            cl_DVT.OptionsColumn.AllowEdit = false;
            cl_DVT.AppearanceCell.TextOptions.HAlignment = HorzAlignment.Center;
            cl_DVT.AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Center;

            //Columns TangKeKhoang
            var cl_TangKeKhoang = gridView1.Columns.ColumnByFieldName("TangKeKhoang");
            cl_TangKeKhoang.Width = 60;
            cl_TangKeKhoang.OptionsColumn.AllowEdit = false;
            cl_TangKeKhoang.Caption = "Vị trí";
            cl_TangKeKhoang.AppearanceCell.TextOptions.HAlignment = HorzAlignment.Center;
            cl_TangKeKhoang.AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Center;

            //Columns Gia
            var cl_Gia = gridView1.Columns.ColumnByFieldName("Gia");
            cl_Gia.Width = 120;
            cl_Gia.OptionsColumn.AllowEdit = false;
            cl_Gia.Caption = "Đơn giá";
            cl_Gia.AppearanceCell.TextOptions.HAlignment = HorzAlignment.Far;
            cl_Gia.AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Center;
            cl_Gia.DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
            cl_Gia.DisplayFormat.FormatString = "n0";

            //Columns ThanhTien
            var cl_ThanhTien = gridView1.Columns.ColumnByFieldName("ThanhTien");
            cl_ThanhTien.Width = 120;
            cl_ThanhTien.Caption = "Thành tiền";
            cl_ThanhTien.OptionsColumn.AllowEdit = false;
            cl_ThanhTien.AppearanceCell.TextOptions.HAlignment = HorzAlignment.Far;
            cl_ThanhTien.AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Center;
            cl_ThanhTien.DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
            cl_ThanhTien.DisplayFormat.FormatString = "n0";
            cl_ThanhTien.Summary.Add(DevExpress.Data.SummaryItemType.Sum, "ThanhTien", "{0:n0}");


            //Columns SLTonKH
            var cl_SLTonKH = gridView1.Columns.ColumnByFieldName("SLTonKH");
            cl_SLTonKH.Width = 70;
            cl_SLTonKH.OptionsColumn.AllowEdit = false;
            cl_SLTonKH.Caption = "Tồn KH";
            cl_SLTonKH.AppearanceCell.TextOptions.HAlignment = HorzAlignment.Far;
            cl_SLTonKH.AppearanceHeader.TextOptions.HAlignment = HorzAlignment.Center;
            cl_SLTonKH.DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
            cl_SLTonKH.DisplayFormat.FormatString = "n0";
        }

        private void btnExcel_Click(object sender, EventArgs e)
        {
            QLVTDataContext dc = new QLVTDataContext();
            //Chuyen so lieu tu excel sang store and don vi
            //Microsoft.Office.Interop.Excel._Application oXL; 
            object xlApp;

            //Excel._Application xlApp;
            Workbook xlBook;
            Worksheet xlSheet;

            //xlApp = new Excel._Application();
            Type t = Type.GetTypeFromProgID("Excel.Application");
            xlApp = System.Activator.CreateInstance(t);

            String link = System.Windows.Forms.Application.StartupPath + "\\TONGHOPHANGTON_template.xltx";
            xlBook = ((Microsoft.Office.Interop.Excel.Application)xlApp).Workbooks.Open(link);
            xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets.get_Item(1);
            ((Microsoft.Office.Interop.Excel.Application)xlApp).Visible = true;

            // TIÊU ĐỀ
            String TieuDe = "BÁO CÁO TỔNG HỢP TỒN " + lkeKho.Text.ToUpper();            
            xlSheet.Cells[2, 1] = TieuDe;
            

            int row0 = 5;
            int i = 1;
            String MaLoaiVatTu = "";
            Excel.Range mRange;
            foreach (dynamic item in listTon)
            {
                //1. STT                
                //mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 1], xlSheet.Cells[row0 + i, 1]];
                //mRange.Value = i;
                //mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                CVatTu m_vattu = new CVatTu();
                if (i==1)
                {
                    MaLoaiVatTu = item.MaLoaiVatTu;
                    xlSheet.Cells[row0 + i, 5] = m_vattu.SelectRecordByMaVatTu(MaLoaiVatTu).VatTu;
                    // Border
                    mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 1], xlSheet.Cells[row0 + i, 15]];
                    var border1 = mRange.Borders[Excel.XlBordersIndex.xlEdgeBottom];
                    border1.LineStyle = Excel.XlLineStyle.xlDot;
                    border1.Weight = Excel.XlBorderWeight.xlThin;

                    mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 5], xlSheet.Cells[row0 + i, 5]];
                    mRange.Font.Bold = true;
                    mRange.Font.Color = Color.Red;

                    i++;
                }
                else
                {
                    if(MaLoaiVatTu != item.MaLoaiVatTu)
                    {
                        MaLoaiVatTu = item.MaLoaiVatTu;
                        xlSheet.Cells[row0 + i, 5] = m_vattu.SelectRecordByMaVatTu(MaLoaiVatTu).VatTu;

                        // Border
                        mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 1], xlSheet.Cells[row0 + i, 15]];
                        var border2 = mRange.Borders[Excel.XlBordersIndex.xlEdgeBottom];
                        border2.LineStyle = Excel.XlLineStyle.xlDot;
                        border2.Weight = Excel.XlBorderWeight.xlThin;

                        mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 5], xlSheet.Cells[row0 + i, 5]];
                        mRange.Font.Bold = true;
                        mRange.Font.Color = Color.Red;

                        i++;
                    }
                }



                //5. Vat Tu                
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 5], xlSheet.Cells[row0 + i, 5]];
                mRange.Value = item.VatTu;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

                //16. MaMatHang                
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 17], xlSheet.Cells[row0 + i, 17]];
                mRange.Value = item.ID_MatHang;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

                //6. Ma Ky Hieu                
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 6], xlSheet.Cells[row0 + i, 6]];
                mRange.Value = item.MaKyHieu;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

                //7. DVT
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 7], xlSheet.Cells[row0 + i, 7]];
                mRange.Value = item.DVT;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

                //8. Gia
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 8], xlSheet.Cells[row0 + i, 8]];
                mRange.Value = item.Gia;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
                mRange.NumberFormat = "#,###";

                //9. SLTonKH
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 9], xlSheet.Cells[row0 + i, 9]];
                mRange.Value = item.SLTonKH;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                mRange.NumberFormat = "#,###";

                //10. CCL
                int vt_CCL = 10;
                if (item.CCL == "1")
                    vt_CCL = 10;
                else if (item.CCL == "2")
                    vt_CCL = 11;
                else if (item.CCL == "3a")
                    vt_CCL = 12;
                else if (item.CCL == "3b")
                    vt_CCL = 13;
                else
                    MessageBox.Show("Vật tư này không có cấp chất lượng");


                mRange = xlSheet.Range[xlSheet.Cells[row0 + i, vt_CCL], xlSheet.Cells[row0 + i, vt_CCL]];
                mRange.Value = item.SLTonKH;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;

                //7. ThanhTien
                //mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 7], xlSheet.Cells[row0 + i, 7]];
                //mRange.Value = item.ThanhTien;
                //mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
                //mRange.NumberFormat = "#,###";

                //4. Nguồn hạch toán
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 4], xlSheet.Cells[row0 + i, 4]];
                mRange.Value = item.KH_NguonVatTu;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

                //14. Nam SX
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 14], xlSheet.Cells[row0 + i, 14]];
                mRange.Value = item.NamSX;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;                

                //1. So The Kho
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 1], xlSheet.Cells[row0 + i, 1]];
                mRange.Value = item.SoTheKho;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

                //2. Vi Tri
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 2], xlSheet.Cells[row0 + i, 2]];
                mRange.Value = item.TangKeKhoang;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

                //3. So Hop Dong (Nguon Goc)
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 3], xlSheet.Cells[row0 + i, 3]];
                mRange.Value = item.SoHopDong;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

                // Border
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i, 1], xlSheet.Cells[row0 + i, 15]];
                var border = mRange.Borders[Excel.XlBordersIndex.xlEdgeBottom];
                border.LineStyle = Excel.XlLineStyle.xlDot;
                border.Weight = Excel.XlBorderWeight.xlThin;

                i++;
            }

            // Border cuối
            mRange = xlSheet.Range[xlSheet.Cells[row0 + i - 1, 1], xlSheet.Cells[row0 + i - 1, 15]];
            var border_cuoi = mRange.Borders[Excel.XlBordersIndex.xlEdgeBottom];
            border_cuoi.LineStyle = Excel.XlLineStyle.xlContinuous;
            border_cuoi.Weight = Excel.XlBorderWeight.xlThin;

            //Ngay Bao Cao
            String NgayBaoCao = "Ngày " + DateTime.Now.Day.ToString() + " tháng " + DateTime.Now.Month.ToString() + " năm " + DateTime.Now.Year.ToString();
            xlSheet.Cells[14, i] = NgayBaoCao;

            // Border các cột
            for (int col = 1; col <= 16; col++)
            {
                mRange = xlSheet.Range[xlSheet.Cells[row0, col], xlSheet.Cells[row0 + i - 1, col]];
                var border_col = mRange.Borders[Excel.XlBordersIndex.xlEdgeLeft];
                border_col.LineStyle = Excel.XlLineStyle.xlContinuous;
                border_col.Weight = Excel.XlBorderWeight.xlThin;
            }

        }       

        private void lkeKho_VisibleChanged(object sender, EventArgs e)
        {
            lkeKho.Properties.PopulateColumns();
            try {
                lkeKho.Properties.Columns["ID_Kho"].Visible = false;
                lkeKho.Properties.Columns["ID_DonVi"].Visible = false;
                lkeKho.Properties.Columns["KH_Kho"].Visible = false;
            }
            catch { };
            
        }

        private void lkeKho_EditValueChanged(object sender, EventArgs e)
        {
            try {
                int ID_Kho = Convert.ToInt32(lkeKho.EditValue);
                KhoiTaoBaoCao(ID_Kho); 
            }
            catch { };
            
        }
    }
}
